package com.hotelManager.www.dao;

import com.hotelManager.www.po.ShopBlacklist;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * ShopBlacklist DAO 类
 * 实现商家黑名单的数据库操作。
 */
public class CustomerShopBlacklistDao {

    /**
     * 添加商家到黑名单
     * @param conn 数据库连接
     * @param blacklist 黑名单记录
     * @return 插入是否成功
     * @throws SQLException 如果数据库操作失败
     */
    public boolean addToBlacklist(Connection conn, ShopBlacklist blacklist) throws SQLException {
        String sql = "INSERT INTO shop_blacklist (user_id, shop_id, created_time) VALUES (?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, blacklist.getUserId());
            ps.setInt(2, blacklist.getShopId());
            ps.setTimestamp(3, blacklist.getCreatedTime());
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 添加商家到黑名单时，手动验证 userId 和 shopId 的有效性。
     * @param conn 数据库连接
     * @param blacklist 黑名单记录
     * @return 插入是否成功
     * @throws SQLException 如果数据库操作失败
     */
    public boolean addToBlacklistWithValidation(Connection conn, ShopBlacklist blacklist) throws SQLException {
        // 验证 userId 是否存在
        String userCheckSql = "SELECT COUNT(*) FROM users WHERE id = ?";
        try (PreparedStatement userCheckStmt = conn.prepareStatement(userCheckSql)) {
            userCheckStmt.setInt(1, blacklist.getUserId());
            try (ResultSet rs = userCheckStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) == 0) {
                    throw new SQLException("用户ID不存在: " + blacklist.getUserId());
                }
            }
        }

        // 验证 shopId 是否存在
        String shopCheckSql = "SELECT COUNT(*) FROM shop WHERE id = ?";
        try (PreparedStatement shopCheckStmt = conn.prepareStatement(shopCheckSql)) {
            shopCheckStmt.setInt(1, blacklist.getShopId());
            try (ResultSet rs = shopCheckStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) == 0) {
                    throw new SQLException("商家ID不存在: " + blacklist.getShopId());
                }
            }
        }

        // 插入黑名单记录
        String sql = "INSERT INTO shop_blacklist (user_id, shop_id, created_time, reason) VALUES (?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, blacklist.getUserId());
            ps.setInt(2, blacklist.getShopId());
            ps.setTimestamp(3, blacklist.getCreatedTime());
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 从黑名单中移除商家
     * @param conn 数据库连接
     * @param userId 用户ID
     * @param shopId 商家ID
     * @return 删除是否成功
     * @throws SQLException 如果数据库操作失败
     */
    public boolean removeFromBlacklist(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "DELETE FROM shop_blacklist WHERE user_id = ? AND shop_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, shopId);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 检查商家是否在用户的黑名单中
     * @param conn 数据库连接
     * @param userId 用户ID
     * @param shopId 商家ID
     * @return 是否在黑名单中
     * @throws SQLException 如果数据库操作失败
     */
    public boolean isShopBlacklisted(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM shop_blacklist WHERE user_id = ? AND shop_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, shopId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }

    /**
     * 获取用户的商家黑名单
     * @param conn 数据库连接
     * @param userId 用户ID
     * @return 商家黑名单列表
     * @throws SQLException 如果数据库操作失败
     */
    public List<ShopBlacklist> getUserBlacklist(Connection conn, int userId) throws SQLException {
        String sql = "SELECT * FROM shop_blacklist WHERE user_id = ? ORDER BY created_time DESC";
        List<ShopBlacklist> blacklist = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    ShopBlacklist item = new ShopBlacklist();
                    item.setId(rs.getInt("id"));
                    item.setUserId(rs.getInt("user_id"));
                    item.setShopId(rs.getInt("shop_id"));
                    item.setCreatedTime(rs.getTimestamp("created_time"));
                    blacklist.add(item);
                }
            }
        }
        return blacklist;
    }
}